﻿<?xml version="1.0" encoding="utf-8"?>
<!-- Schema.xml - BasicWebPlayer extensions to the Microsoft Learning Components (MLC) base schema -->
<StoreSchema xmlns="urn:schemas-microsoft-com:learning-components:learning-store-schema">
    <ExtendItemType Name="PackageItem">
        <Properties>
            <Property Name="Owner" Type="ItemIdentifier" ReferencedItemTypeName="UserItem" Nullable="true">
                <Default Null="true" />
            </Property>
            <Property Name="FileName" Type="String" Length="260">
                <Default />
            </Property>
            <Property Name="UploadDateTime" Type="DateTime" Nullable="true">
                <Default Null="true"/>
            </Property>
        </Properties>
        <GrantUpdateRight>
            <Expression>CASE WHEN @PackageFormat$Changed=0 AND @Location$Changed=0 AND @Manifest$Changed=0 AND
                EXISTS(SELECT * FROM PackageItem WHERE Id=@Id AND Owner IS NULL) AND @Owner$Changed=1 AND
                EXISTS(SELECT * FROM UserItem WHERE Id=@Owner AND [Key]=@UserKey) THEN 1 ELSE 0 END</Expression>
        </GrantUpdateRight>
    </ExtendItemType>
    <ExtendItemType Name="UserItem">
        <GrantAddRight>
            <Expression>CASE WHEN @Key=@UserKey THEN 1 ELSE 0 END</Expression>
        </GrantAddRight>
    </ExtendItemType>
    <ExtendRight Name="AddPackageReferenceRight">
        <Grant>
            <Expression>1</Expression>
        </Grant>
    </ExtendRight>
    <ExtendRight Name="RemovePackageReferenceRight">
        <Grant>
            <Expression>CASE WHEN EXISTS(SELECT * FROM PackageItem INNER JOIN UserItem ON PackageItem.Owner=UserItem.Id WHERE PackageItem.Id=@PackageId AND UserItem.[Key]=@UserKey) THEN 1 ELSE 0 END</Expression>
        </Grant>
    </ExtendRight>
    <ExtendRight Name="CreateAttemptRight">
        <Grant>
            <Expression>CASE WHEN
                (EXISTS(SELECT * FROM ActivityPackageItem INNER JOIN PackageItem ON ActivityPackageItem.PackageId=PackageItem.Id INNER JOIN UserItem ON PackageItem.Owner=UserItem.Id WHERE ActivityPackageItem.Id=@RootActivityId AND UserItem.[Key]=@UserKey)) AND
                (EXISTS(SELECT * FROM UserItem WHERE UserItem.[Key]=@UserKey AND Id=@LearnerId)) THEN 1 ELSE 0 END</Expression>
        </Grant>
    </ExtendRight>
    <ExtendRight Name="DeleteAttemptRight">
        <Grant>
            <Expression>CASE WHEN EXISTS(SELECT * FROM AttemptItem INNER JOIN UserItem ON AttemptItem.LearnerId=UserItem.Id WHERE AttemptItem.Id=@AttemptId AND UserItem.[Key]=@UserKey) THEN 1 ELSE 0 END</Expression>
        </Grant>
    </ExtendRight>
    <ExtendRight Name="ExecuteSessionRight">
        <Grant>
            <Expression>CASE WHEN EXISTS(SELECT * FROM AttemptItem INNER JOIN UserItem ON AttemptItem.LearnerId=UserItem.Id WHERE AttemptItem.Id=@AttemptId AND UserItem.[Key]=@UserKey) THEN 1 ELSE 0 END</Expression>
        </Grant>
    </ExtendRight>
    <View Name="Me">
        <Columns>
            <Column Name="UserId" Type="ItemIdentifier" ReferencedItemTypeName="UserItem"/>
            <Column Name="UserName" Type="String" />
        </Columns>
        <Implementation>
            SELECT Id AS UserId,
                   Name AS UserName
            FROM UserItem
            WHERE [Key]=@UserKey
        </Implementation>
        <GrantQueryRight>
            <Expression>1</Expression>
        </GrantQueryRight>
    </View>
    <View Name="MyAttemptsAndPackages">
        <Columns>
            <Column Name="PackageId" Type="ItemIdentifier" ReferencedItemTypeName="PackageItem"/>
            <Column Name="PackageFileName" Type="String"/>
            <Column Name="OrganizationId" Type="ItemIdentifier" ReferencedItemTypeName="ActivityPackageItem"/>
            <Column Name="OrganizationTitle" Type="String"/>
            <Column Name="AttemptId" Type="ItemIdentifier" ReferencedItemTypeName="AttemptItem"/>
            <Column Name="UploadDateTime" Type="DateTime"/>
            <Column Name="AttemptStatus" Type="Enum" EnumName="AttemptStatus"/>
            <Column Name="TotalPoints" Type="Single"/>
        </Columns>
        <Implementation>
			SELECT PackageItem.Id AS PackageId,
				   PackageItem.FileName AS PackageFileName,
				   ActivityPackageItem.Id AS OrganizationId,
				   ActivityPackageItem.Title AS OrganizationTitle,
				   AttemptItem.Id AS AttemptId,
				   PackageItem.UploadDateTime,
				   AttemptItem.AttemptStatus,
				   AttemptItem.TotalPoints
			FROM ActivityPackageItem
			INNER JOIN PackageItem ON ActivityPackageItem.PackageId = PackageItem.Id
			LEFT OUTER JOIN ActivityAttemptItem ON ActivityPackageItem.Id = ActivityAttemptItem.ActivityPackageId
			LEFT OUTER JOIN AttemptItem ON ActivityAttemptItem.AttemptId = AttemptItem.Id
			INNER JOIN UserItem ON PackageItem.Owner = UserItem.Id
			WHERE (ActivityPackageItem.ParentActivityId IS NULL)
			AND (UserItem.[Key] = @UserKey)
		</Implementation>
        <GrantQueryRight>
            <Expression>1</Expression>
        </GrantQueryRight>
	</View>
	<View Name="SequencingLog">
	    <Parameters>
	        <Parameter Name="AttemptId" Type="ItemIdentifier" ReferencedItemTypeName="AttemptItem"/>
	    </Parameters>
	    <Columns>
	        <Column Name="Id" Type="ItemIdentifier" ReferencedItemTypeName="SequencingLogEntryItem"/>
	        <Column Name="Timestamp" Type="DateTime"/>
	        <Column Name="EventType" Type="Enum" EnumName="SequencingEventType"/>
	        <Column Name="NavigationCommand" Type="Enum" EnumName="NavigationCommand"/>
	        <Column Name="Message" Type="String"/>	        
	    </Columns>
	    <Implementation>
	        SELECT Id,
	               Timestamp,
	               EventType,
	               NavigationCommand,
	               Message
	        FROM SequencingLogEntryItem
	        WHERE AttemptId=@AttemptId
	    </Implementation>
	    <GrantQueryRight>
            <Expression>CASE WHEN EXISTS(SELECT * FROM AttemptItem INNER JOIN UserItem ON AttemptItem.LearnerId=UserItem.Id WHERE AttemptItem.Id=@AttemptId AND UserItem.[Key]=@UserKey) THEN 1 ELSE 0 END</Expression>
	    </GrantQueryRight>
	</View>
</StoreSchema>
